package com.zjz.dao.user;

import com.mysql.cj.util.StringUtils;
import com.zjz.dao.BaseDao;
import com.zjz.pojo.Role;
import com.zjz.pojo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao{



    //得到登录的用户
    public User getLoginUser(Connection connection, String userCode) throws SQLException {

        PreparedStatement pstm = null;
        ResultSet rs = null;
        User user = null;
        if(connection != null){
            //还是先写个sql，然后再放进连接里
            String  sql = "select * from smbms_user where userCode = ?";
            Object[] params = {userCode};

            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            if(rs.next()){
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setCreationDate(rs.getTimestamp("creationDate"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setModifyDate(rs.getTimestamp("modifyDate"));

            }
            BaseDao.closeResource(null,pstm,rs);

        }
        return user;
    }


    //修改当前用户密码
    public int updatePsd(Connection connection, int id, String password) throws SQLException {

         PreparedStatement pstm = null;

         int excute = 0;
         if(connection!=null){
             String sql = "update smbms_user set userPassword = ? where id = ?";
             Object params[] = {password,id};
             excute = BaseDao.execute(connection,pstm,sql,params);
             BaseDao.closeResource(null,pstm,null);
         }
        return excute;

    }


    // 根据用户名或者角色查询用户总数（最难的SQL）---获取数量后才能执行分页
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {

        PreparedStatement pstm = null;
        ResultSet  rs = null;
        int count = 0;

        if(connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select COUNT(1) AS count FROM smbms_user u,smbms_role r where u.userRole = r.id");
            ArrayList<Object> list = new ArrayList<Object>(); // 存放我们的参数

            if(!StringUtils.isNullOrEmpty(username)){
                sql.append(" and u.username like ?");
                list.add("%"+username+"%");
            }

            if(userRole>0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }

            // 怎么把list转换为数组
            Object[] params = list.toArray();
            System.out.println("UserDaoImpl->getUserCount:" + sql.toString()); // 输出后完整的语句
            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);

            if(rs.next()){
                 count = rs.getInt("count");
            }
            BaseDao.closeResource(null,pstm,rs);

        }

        return count;
    }


    public List<User> getUserList(Connection connection, String username, int userRole, int currentPageNo, int pageSize) throws Exception {
        PreparedStatement pstm = null;
        ResultSet  rs = null;
        List<User> userList = new ArrayList<User>();


        if(connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select u.*,r.roleName AS userRoleName FROM smbms_user u,smbms_role r where u.userRole = r.id");
            List<Object> list = new ArrayList<Object>(); // 存放我们的参数

            if(!StringUtils.isNullOrEmpty(username)){
                sql.append(" and u.username like ?");
                list.add("%"+username+"%");
            }

            if(userRole>0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }

            sql.append(" order by creationDate DESC limit ?,?");
            currentPageNo = (currentPageNo-1) * pageSize;
            list.add(currentPageNo);
            list.add(pageSize);

            // 怎么把list转换为数组
            Object[] params = list.toArray();
            System.out.println("UserDaoImpl->getUserList:" + sql.toString()); // 输出后完整的语句

            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);

            while (rs.next()){
                User _user = new User();
                _user.setId(rs.getInt("id"));
                _user.setUserCode(rs.getString("userCode"));
                _user.setUserName(rs.getString("userName"));
//                _user.setUserPassword(rs.getString("userPassword"));
                _user.setGender(rs.getInt("gender"));
                _user.setBirthday(rs.getDate("birthday"));
                _user.setPhone(rs.getString("phone"));
                _user.setAddress(rs.getString("address"));
                _user.setUserRole(rs.getInt("userRole"));
                _user.setUserRoleName(rs.getString("userRoleName"));

                userList.add(_user);
/*                _user.setCreatedBy(rs.getInt("createdBy"));
                _user.setCreationDate(rs.getTimestamp("creationDate"));
                _user.setModifyBy(rs.getInt("modifyBy"));
                _user.setModifyDate(rs.getTimestamp("modifyDate"));*/
            }
            BaseDao.closeResource(null,pstm,rs);

        }

        return userList;
    }



}
